#include "baseDB.h"

BaseDB::BaseDB(QObject *parent) :
    QObject(parent)
    ,sql_query(NULL)
    ,dbPath("")
{

}

bool BaseDB::open(QString db_name,QString db_type)
{
    database = QSqlDatabase::addDatabase(db_type);      //设置数据库类型
    database.setDatabaseName(db_name);
    if (!database.open())
    {
        qDebug()<<Q_FUNC_INFO<<"Error: Failed to connect database！" << database.lastError();
        return 0;
    }   //qDebug()<<Q_FUNC_INFO<<"Succeed to connect database:"<<db_name;

    //数据库信息保存
    dbPath = db_name;   dbType = db_type;

    //QSqlQuery关联数据库，后续所有操作使用 sql_query
    sql_query = new QSqlQuery(database);

    return 1;
}

void BaseDB::close()
{
    database.close();
    dbPath.clear();
    dbType.clear();
    relaseAllModel();
    if(sql_query != NULL)
    {
        delete sql_query;
        sql_query = NULL;
    }
}

bool BaseDB::creatTab(QString tabStruct)
{
    QSqlQuery sql_query(database);
    if(sql_query == NULL)   return 0;
    if(!sql_query->exec(tabStruct))
    {
        qDebug()<<Q_FUNC_INFO<<"Error: Fail to create table."<< sql_query->lastError();
        return 0;
    }
    else
    {
        qDebug()<<Q_FUNC_INFO<<"Table created!";
        return 1;
    }
}

bool BaseDB::deleteTab(QString tabName)
{
    if(sql_query == NULL)   return 0;
    QString srtSQL = QString("DROP TABLE %1").arg(tabName);
    if(!sql_query->exec(srtSQL))
    {
        qDebug()<<Q_FUNC_INFO<<"Error: Fail to delete table."<< sql_query->lastError();
        return 0;
    }
    else
    {
        qDebug()<<Q_FUNC_INFO<<"Table delete!";
        return 1;
    }
}

QStringList BaseDB::getTableNames()     //获取数据库表名链表
{
    QStringList tabNames = database.tables();
    if(tabNames.contains("sqlite_sequence"))
        tabNames.removeAt(tabNames.indexOf("sqlite_sequence"));
    return tabNames;
}

bool BaseDB::insetData(QString tabName, QStringList indexList, QStringList dataList)    //待测试
{
    if(sql_query == NULL)   return 0;
    if(indexList.isEmpty() || dataList.isEmpty())   return 0;   //输入段名或数据为空
    if(indexList.size() != dataList.size()) return 0;       //输入数据和段名数量对不上

    QString listIndex = indexList.join(",");
    QString indexData = dataList.join(",");

    QString strSQL = QString("INSERT INTO %1 (%2) VALUES (%3)").arg(tabName).arg(listIndex).arg(indexData);
    if(!sql_query->exec(strSQL))
    {
        qDebug() << sql_query->lastError();
        return 0;
    }
    qDebug() << "inserted data successed!";
    return 1;
}

bool BaseDB::insetData(QString tabName, QStringList dataList)       //待测试
{
    if(sql_query == NULL)   return 0;
    if(dataList.isEmpty())   return 0;   //输入段名或数据为空
    QString indexData = dataList.join(",");
    QString strSQL = QString("INSERT INTO %1 VALUES (%2)").arg(tabName).arg(indexData);
    if(!sql_query->exec(strSQL))
    {
        qDebug() << sql_query->lastError();
        return 0;
    }
    qDebug() << "inserted data successed!";
    return 1;
}

bool BaseDB::updateData(QString tabName, QString filterSql, QString indexData)  //updateData(tabName, "id = 0", "name = jt");   待测试
{
    if(sql_query == NULL)   return 0;
    QString strSQL = QString("UPDATE %1 SET %2 WHERE %3 ").arg(tabName).arg(indexData).arg(filterSql);
    if(!sql_query->exec(strSQL ))
    {
        qDebug() << sql_query->lastError();
        return 0;
    }
    qDebug() << "updated!";
    return 1;
}


QSqlTableModel *BaseDB::getTableModel(QString tableName)        //待测试
{
    QStringList tableNameList = getTableNames();
    if(!tableNameList.contains(tableName))  //如果表格不存在则返回空
        return NULL;
    QSqlTableModel *model = new QSqlTableModel(this,database);
    model->select();

    model->setHeaderData(1, Qt::Horizontal, "Name");
    while(model->canFetchMore())
    {
        model->fetchMore();
    }
    model->setTable(tableName);
    tableModelList.append(model);
    return model;
}
QSqlTableModel *BaseDB::getTableModel(int i)    //待测试
{
    QStringList tableNameList = getTableNames();
    if(i >= tableNameList.size() || i < 0)
        return NULL;
    QSqlTableModel *model = new QSqlTableModel(this,database);
    model->setTable(tableNameList.at(i));
    tableModelList.append(model);
    return model;
}

bool BaseDB::relaseTableModel(QString tableName)    //待测试 ！！！！！！
{
    tableName = tableName.trimmed();
    for(int i = 0;i < tableModelList.size();i ++)
    {
        if(tableName == tableModelList.at(i)->tableName())
        {
            QSqlTableModel *ptr = tableModelList.at(i);
            if(ptr != NULL) delete ptr;
            tableModelList.removeAt(i);
            return 1;
        }
    }
    return 0;
}
void BaseDB::relaseAllModel()   //待测试 ！！！！！！
{
    //只读遍历器
    for(int i = 0;i < tableModelList.size();i ++)
    {
        QSqlTableModel *ptr = tableModelList.at(i);
        if(ptr != NULL) delete ptr;
    }
    tableModelList.clear();
}

QStringList BaseDB::getTableIndexName(QString tabNmae)
{
    QStringList indexList;
    QString str = "PRAGMA table_info(" + tabNmae + ")";
    sql_query->prepare(str);
    if (sql_query->exec())
    {
        //sql_query->value(0):indexNum
        //sql_query->value(1):indexName
        //sql_query->value(2):indexType
        while (sql_query->next())
            indexList<<sql_query->value(1).toString();
    }
    else
        qDebug() << sql_query->lastError();
    return indexList;
}

QStringList BaseDB::getTableIndexType(QString tabNmae)
{
    QStringList indexList;
    QString str = "PRAGMA table_info(" + tabNmae + ")";
    sql_query->prepare(str);
    if (sql_query->exec())
    {
        while (sql_query->next())
            indexList<<sql_query->value(2).toString();
    }
    else
        qDebug() << sql_query->lastError();
    return indexList;
}






